﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace quanlysinhvien
{
    public partial class Form1 : Form
    {
        SqlConnection conn;
        SqlCommand cmd;
        SqlDataAdapter da;
        DataTable dt;
        DataSet ds;

        public static DataTable dt1;

        public Form1()
        {
            InitializeComponent();
        }
        

        private void loadComboSubject()
        {
            conn = Connect.getConnection();
            conn.Open();
            string sql = "select * from TblSubject";
            cmd = new SqlCommand(sql, conn);
            da = new SqlDataAdapter(cmd);
            dt = new DataTable();
            da.Fill(dt);
            cbSubject.DataSource = dt;
            cbSubject.DisplayMember = "Subject_Name";
            cbSubject.ValueMember = "Subject_Code";
            cbSubject.Text = "Select Subject";
        }
        private void loadComboFaculty()
        {
            conn = Connect.getConnection();
            conn.Open();
            string sql = "Select * from TblFaculty";
            cmd = new SqlCommand(sql, conn);
            da = new SqlDataAdapter(cmd);
            dt = new DataTable();
            da.Fill(dt);
            cbFaculty.DataSource = dt;
            cbFaculty.DisplayMember = "Faculty_Name";
            cbFaculty.ValueMember = "Faculty_Code";
            cbFaculty.Text = "Select Faculty";

        }
        private void LoadComboClass()
        {
            string fac = cbFaculty.SelectedValue.ToString();
            conn = Connect.getConnection();
            conn.Open();
            string sql = "Select * from TblClass where Faculty_Code='" + fac + "'";
            cmd = new SqlCommand(sql, conn);
            da = new SqlDataAdapter(cmd);
            dt = new DataTable();
            da.Fill(dt);
            da.Dispose();
            cbClass.DataSource = dt;
            cbClass.DisplayMember = "Class_Code";
            cbClass.ValueMember = "Class_Code";
            conn.Close();
            cbClass.Text = "Select class";
        }
        private void loaddgclass()
        {
            string sub = cbSubject.SelectedValue.ToString();
            string clas = cbClass.SelectedValue.ToString();
            conn = Connect.getConnection();
            conn.Open();
            string sql = " select TblStu_Sub.Student_Code,TblClass.Class_Code,TblStudents.Student_Name,TblStudents.Student_DOB,TblStudents.Student_Sex,TblStu_Sub.Mark from TblStudents,TblClass,TblStu_Sub where TblStu_Sub.Student_Code=TblStudents.Student_Code and TblClass.Class_Code=TblStudents.Class_Code and TblStu_Sub.Subject_Code='" + sub + "' and TblStudents.Class_Code='" + clas + "'";
            cmd = new SqlCommand(sql, conn);
            da = new SqlDataAdapter(cmd);
            ds = new DataSet();
            da.Fill(ds, "stu");
            dgDiem.DataSource = ds.Tables[0];
            conn.Close();

        }
        private void loaddgfaculty()
        {
            string sub = cbSubject.SelectedValue.ToString();
            string fac = cbFaculty.SelectedValue.ToString();
            conn = Connect.getConnection();
            conn.Open();
            string sql = " select TblStu_Sub.Student_Code,TblClass.Class_Code,TblStudents.Student_Name,TblStudents.Student_DOB,TblStudents.Student_Sex,TblStu_Sub.Mark,TblFaculty.Faculty_Code from TblStudents,TblClass,TblStu_Sub,TblFaculty where TblStu_Sub.Student_Code=TblStudents.Student_Code and TblClass.Class_Code=TblStudents.Class_Code and TblFaculty.Faculty_Code=TblClass.Faculty_Code and TblStu_Sub.Subject_Code='" + sub + "' and TblFaculty.Faculty_Code='" + fac + "'";
            cmd = new SqlCommand(sql, conn);
            da = new SqlDataAdapter(cmd);
            ds = new DataSet();
            da.Fill(ds, "stu");
            dgDiem.DataSource = ds.Tables[0];
            conn.Close();
        }

        private void Form1_Load(object sender, EventArgs e)
        {
            loadComboFaculty();
            loadComboSubject();
        }
      
        private void cbClass_SelectedIndexChanged(object sender, EventArgs e)
        {
            
        }

        private void cbFaculty_SelectedIndexChanged(object sender, EventArgs e)
        {
            LoadComboClass();
        }
        private void Enablebtn(bool editing)
        { 
        
        }
 
        private void btnBack_Click(object sender, EventArgs e)
        {
            FormMain FormMain = new FormMain();
            FormMain.Show();
            this.Hide();
        }

        private void btnFaculty_Click(object sender, EventArgs e)
        {
            loaddgfaculty();
            lbResult.Text = "Result by Faculty:";
        }

        private void btnClass_Click(object sender, EventArgs e)
        {
            loaddgclass();
            lbResult.Text = "Result by Class:";
        }

        private void btnCreate_Click(object sender, EventArgs e)
        {

        }
    }
}
